import sys
from PyQt5.QtWidgets import QApplication, QMainWindow
import pyodbc
from PyQt5 import QtGui, QtWidgets
from ManegeSystem import Ui_MainWindow


class MyMainWindow(QMainWindow, Ui_MainWindow):
    def __init__(self):
        super(MyMainWindow, self).__init__()
        self.setupUi(self)
        # 功能链接槽函数

        # 航班表部分
        self.pushButton_f1.clicked.connect(self.flight_view)  # 刷新显示
        self.pushButton_f3.clicked.connect(self.flight_insert)  # 插入按钮
        self.pushButton_f4.clicked.connect(self.flight_delete)  # 删除按钮
        self.pushButton_f2.clicked.connect(self.flight_modify)  # 修改按钮
        # 旅客表部分
        self.pushButton_v1.clicked.connect(self.visitor_view)  # 刷新显示
        self.pushButton_v3.clicked.connect(self.visitor_insert)  # 插入按钮
        self.pushButton_v4.clicked.connect(self.visitor_delete)  # 删除按钮
        self.pushButton_v2.clicked.connect(self.visitor_modify)  # 修改按钮
        # 账户表部分
        self.pushButton_c1.clicked.connect(self.account_view)  # 刷新显示
        self.pushButton_c3.clicked.connect(self.account_insert)  # 插入按钮
        self.pushButton_c4.clicked.connect(self.account_delete)  # 删除按钮
        self.pushButton_c2.clicked.connect(self.account_modify)  # 修改按钮
        # 查询部分
        self.pushButton_c.clicked.connect(self.find_c)

        # 树节点监听事件
        self.treeWidget_2.clicked.connect(self.onClicked)
        # 统计数据条数
        self.i = 0

    def connect_database(self, sql, show=False):
        try:
            # 链接到sql server
            db = pyodbc.connect(driver='{SQL Server}', server='(local)', database='FlightManagement',
                                trusted_connection='yes')
            cur = db.cursor()
            cur.execute(sql)
            if show == True:
                self.i = [i[0] for i in cur][0]
            # 关闭数据库链接减少占用
            # 必须加.commit()方法？？？？？？？？？？？(未懂 但可行)
            db.commit()
            cur.commit()
            # 关闭数据库链接减少占用
            cur.close()
            db.close()
        except Exception as e:
            print(e)

    def widget_show(self, n, sql, rows, cols):
        try:
            # 多次调用时要清除前面显示的行，不然会数据错行
            self.tableWidget_f.clearContents()
            # 链接到sql server
            db = pyodbc.connect(driver='{SQL Server}', server='(local)', database='FlightManagement',
                                trusted_connection='yes')
            cur = db.cursor()
            cur.execute(sql)
            it = ""
            if n == 'f':
                it = "tableWidget_f"
            elif n == 'v':
                it = "tableWidget_v"
            elif n == 'c':
                it = "tableWidget_c"

            x = 0
            eval("self.{}.setRowCount({})".format(it, rows))  # 设定tableWidget的总行数
            for row in cur:  # pyodbc.row对象可通过列表形式读取或者使用列名读取
                # 循环设置行
                item = QtWidgets.QTableWidgetItem()
                eval("self.{}.setVerticalHeaderItem(-1, item)".format(it))
                # 循环添加数据
                for y in range(cols):
                    eval("self.{}.setItem(x, y, QtWidgets.QTableWidgetItem(str(row[y])))".format(it))
                x += 1

            # 必须加.commit()方法？？？？？？？？？？？(未懂 但可行)
            db.commit()
            cur.commit()
            # 关闭数据库链接减少占用
            cur.close()
            db.close()
        except Exception as e:
            print(e)

    # 树节点点击事件
    def onClicked(self):
        try:
            item = self.treeWidget_2.currentItem()
            print("open {}".format(item.text(0)))
            if item.text(0) == "航班管理":
                self.stackedWidget.setCurrentIndex(3)  # 打开界面v
            elif item.text(0) == "旅客管理":
                self.stackedWidget.setCurrentIndex(1)  # 打开界面c
            elif item.text(0) == "账户管理":
                self.stackedWidget.setCurrentIndex(2)  # 打开界面f
            elif item.text(0) == "查询":
                self.stackedWidget.setCurrentIndex(0)  # 打开界面
            else:
                print('返回主界面')
        except Exception as e:
            print(e)

    # 信息刷新显示功能
    def flight_view(self):
        try:
            sql = "SELECT *  FROM flight ORDER BY 出发日期 DESC"
            self.widget_show('f', sql, 30, 11)

            self.connect_database("SELECT count(航班号) FROM flight", show=True)
            self.numshow_f.setText(str(self.i))

            print(sql)
            print("Flight view-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 航班信息的增添
    def flight_insert(self):
        try:
            le_f1 = self.lineEdit_f1.text()  # 用self.lineEdit.text获取文本框输入值
            le_f2 = self.lineEdit_f2.text()
            le_f3 = self.lineEdit_f3.text()
            le_f4 = self.lineEdit_f4.text()
            le_f5 = self.lineEdit_f5.text()
            le_f6 = self.lineEdit_f6.text()
            le_f7 = self.lineEdit_f7.text()
            le_f8 = self.lineEdit_f8.text()
            le_f9 = self.lineEdit_f9.text()
            le_f10 = self.lineEdit_f10.text()
            le_f11 = self.lineEdit_f11.text()
            insert = [le_f1, le_f2, le_f3, le_f4, le_f5, le_f6, le_f7, le_f8, le_f9, le_f10, le_f11]
            sql = "INSERT INTO flight VALUES ('{}', '{}', {}, {}, '{}', '{}', {}, '{}', '{}', '{}', '{}')".format(
                *insert)
            self.connect_database(sql)
            print(sql)
            print("Flight insert-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 航班信息的删除
    def flight_delete(self):
        try:
            le_f15 = self.lineEdit_f15.text()
            sql = "DELETE FROM flight WHERE 航班号='{}'".format(le_f15)
            self.connect_database(sql)
            print(sql)
            print("Flight delete-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 航班信息的修改
    def flight_modify(self):
        try:
            le_f12 = self.lineEdit_f12.text()
            le_f13 = self.lineEdit_f13.text()
            le_f14 = self.lineEdit_f14.text()

            rows = le_f13.split(",")
            cols = le_f14.split(",")
            data = []
            for i in range(len(cols)):
                if rows[i] == '经济舱票价(B*)' or rows[i] == '头等舱票价(A*)':
                    data.append('[{}]'.format(rows[i]))
                else:
                    data.append(rows[i])
                data.append(cols[i])
            data.append('航班号')
            data.append(le_f12)

            # 问号为占位符，其后跟输入的值
            sql = "UPDATE flight SET " + "{}='{}'," * (len(cols) - 1) + "{}='{}'" + " WHERE {}='{}'"
            sql = sql.format(*data)
            self.connect_database(sql)
            print(sql)
            print("Flight modify-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 旅客信息显示
    def visitor_view(self):
        try:
            sql = "SELECT * FROM visitor ORDER BY 姓名 ASC"
            self.widget_show('v', sql, 1100, 5)
            self.connect_database("SELECT count(姓名) FROM visitor", show=True)
            self.numshow_v.setText(str(self.i))
            print(sql)
            print("Visitor view-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 旅客信息的增添
    def visitor_insert(self):
        try:
            le_v1 = self.lineEdit_v1.text()  # 用self.lineEdit.text获取文本框输入值
            le_v2 = self.lineEdit_v2.text()
            le_v3 = self.lineEdit_v3.text()
            le_v4 = self.lineEdit_v4.text()
            le_v5 = self.lineEdit_v5.text()
            insert = [le_v1, le_v2, le_v3, le_v4, le_v5]

            sql = "INSERT INTO visitor VALUES ('{}', '{}', '{}', '{}', '{}')".format(*insert)
            self.connect_database(sql)
            print(sql)
            print("Visitor insert-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 旅客信息的删除
    def visitor_delete(self):
        try:
            le_v9 = self.lineEdit_v9.text()
            sql = "DELETE FROM visitor WHERE 身份证号='{}'".format(le_v9)
            self.connect_database(sql)
            print(sql)
            print("Visitor delete-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 旅客信息的修改
    def visitor_modify(self):
        try:
            le_v6 = self.lineEdit_v6.text()
            le_v7 = self.lineEdit_v7.text()
            le_v8 = self.lineEdit_v8.text()

            rows = le_v7.split(",")
            cols = le_v8.split(",")
            data = []
            for i in range(len(cols)):
                data.append(rows[i])
                data.append(cols[i])
            data.append('身份证号')
            data.append(le_v6)

            # 问号为占位符，其后跟输入的值
            sql = "UPDATE visitor SET " + "{}='{}'," * (len(cols) - 1) + "{}='{}'" + " WHERE {}='{}'"
            sql = sql.format(*data)
            self.connect_database(sql)
            print(sql)
            print("Visitor modify-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 账户信息显示
    def account_view(self):
        try:
            sql = "SELECT * FROM account ORDER BY 账号 ASC"
            self.widget_show('c', sql, 1100, 2)
            self.connect_database("SELECT count(账号) FROM account", show=True)
            self.numshow_c.setText(str(self.i))
            print(sql)
            print("Account view-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 账户信息的增添
    def account_insert(self):
        try:
            le_c1 = self.lineEdit_c1.text()  # 用self.lineEdit.text获取文本框输入值
            le_c2 = self.lineEdit_c2.text()
            insert = [le_c1, le_c2]
            sql = "INSERT INTO account VALUES ('{}', '{}')".format(*insert)
            self.connect_database(sql)
            print(sql)
            print("Account insert-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 账户信息的删除
    def account_delete(self):
        try:
            le_c5 = self.lineEdit_c5.text()
            sql = "DELETE FROM account WHERE 身份证号='{}'".format(le_c5)
            self.connect_database(sql)
            print(sql)
            print("Account delete-------ok")
            print("\n")
        except Exception as e:
            print(e)

    # 账户信息的修改
    def account_modify(self):
        try:
            le_c3 = self.lineEdit_c3.text()
            le_c4 = self.lineEdit_c4.text()
            sql = "UPDATE account SET 账号='{}' WHERE 身份证号='{}'".format(le_c4, le_c3)
            self.connect_database(sql)
            print(sql)
            print("Account modify-------ok")
            print("\n")
        except Exception as e:
            print(e)

    def find_c(self):
        try:
            le_c = self.lineEdit_c.text()
            if len(le_c) == 18:
                item = "visitor.身份证号"
            else:
                item = "账号"

            # 问号为占位符，其后跟输入的值
            sql = """
            SELECT visitor.姓名,visitor.性别,account.账号,visitor.身份证号,visitor.座位号,flight.* 
            FROM visitor,flight,account 
            WHERE flight.航班号=visitor.航班号 AND account.身份证号=visitor.身份证号 AND {}='{}'
                    """.format(item, le_c)

            # 链接到sql server
            db = pyodbc.connect(driver='{SQL Server}', server='(local)', database='FlightManagement'
                                , trusted_connection='yes', autocommit=True)
            cur = db.cursor()
            cur.execute(sql)
            self.listWidget.clear()
            cols = ['姓名', '性别', '账号', '身份证号', '座位号', '航班号', '所属公司', '经济仓票价(B*)',
                    '头等仓票价(A*)', '起点', '终点', '座位数量', '出发日期', '起飞时间', '到达时间', '飞行时长']
            for row in cur:  # pyodbc.row对象可通过列表形式读取或者使用列名读取
                for x, y in enumerate(row):
                    text = '{}: {}'.format(cols[x], y)
                    self.item = QtWidgets.QListWidgetItem(text)  # 把字符串转化为QListWidgetItem项目对象
                    self.litWidget.addItem(self.item)  # 添加项目
            # 必须加.commit()方法？？？？？？？？？？？(未懂 但可行)
            db.commit()
            cur.commit()
            # 关闭数据库链接减少占用
            cur.close()
            db.close()
            print(sql)
            print("Find all-------ok")
            print("\n")
        except Exception as e:
            print(e)


if __name__ == "__main__":
    app = QApplication(sys.argv)
    win = MyMainWindow()
    win.setWindowTitle("民航售票系统")
    win.setWindowIcon(QtGui.QIcon("icon.ico"))
    win.show()
    sys.exit(app.exec_())
